/**
 * 
 */
package com.magnusart.scrumppm.learning;


import static org.junit.Assert.assertEquals;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

/**
 * @author magnus
 *
 */
public class LearnHowToUseHSQLDB
{

	/**	
	 * Connection handle
	 */
	private Connection c;
	private String ford = "Ford";
	private String toyota = "Toyota";
	private int ford_price = 100;
	private int toy_price = 200;

	/**
	 * @throws java.lang.Exception
	 */
	@Before
	public void setUp() throws Exception {
		try {
			Class.forName("org.hsqldb.jdbcDriver" );
		} catch (Exception e) {
			System.out.println("ERROR: failed to load HSQLDB JDBC driver.");
			e.printStackTrace();
			return;
		}

		c = DriverManager.getConnection("jdbc:hsqldb:mem:testdb", "sa", "");
		//Persistent database
		//c = DriverManager.getConnection("jdbc:hsqldb:file:/home/magnus/Programmering/YAPES/Database/testdb", "sa", "" );
		// Init database
		setUpDatabase();
	}

	/**
	 * @throws java.lang.Exception
	 */
	@After
	public void tearDown() throws Exception {
		Statement st = c.createStatement();

		// db writes out to files and performs clean shuts down
		// otherwise there will be an unclean shutdown
		// when program ends
		st.execute("SHUTDOWN");
		st.close();
		c.close();    // if there are no other open connection

	}

	public void setUpDatabase() throws Exception {
		// make an empty table
		//
		// by declaring the id column IDENTITY, the db will automatically
		// generate unique values for new rows- useful for row keys
		String exp =  "CREATE TABLE sample_table ( id INTEGER IDENTITY, str_col VARCHAR(256), num_col INTEGER)" + 
						"INSERT INTO sample_table(str_col,num_col) VALUES('"+ford+"', "+ford_price+")" + 
						"INSERT INTO sample_table(str_col,num_col) VALUES('"+toyota+"', "+toy_price+")";

		Statement st = c.createStatement();

		int i = st.executeUpdate( exp );    // run the query

		if (i == -1) {
			System.out.println("db error : " );
		}		

		st.close();	
	}

	@Test
	public void shouldBeAbleToConnectToDatabase() throws Exception {
		String exp = "SELECT * FROM sample_table WHERE num_col < 250";

		Statement st = c.createStatement();
		ResultSet rs = st.executeQuery( exp );

        if( rs.next() ) {
        	assertEquals( ford, rs.getString( "str_col" ) );
        	assertEquals( ford_price, rs.getInt( "num_col" ) );
        } else {
        	throw new Exception("First row should exist in resultset!");
        }
        
        if( rs.next() ) {
        	assertEquals( toyota, rs.getString( "str_col" ) );
        	assertEquals( toy_price, rs.getInt( "num_col" ) );
        } else {
        	throw new Exception("Second row should exist in resultset!");
        }
        
        rs.close();
        st.close();

		
	}

}
